In this ‘code-through,’ we’ll delve into the DataTable (DT) package and its application in R for a given dataset. It’s important to note that this package is different from the data.table, an enhanced version of the data.frame. Instead, DT acts as an interface to the JavaScript library DataTables. It’s a robust tool for constructing interactive and intricate tables using HTML or Shiny. We’ll use the simple “Attitude” dataset to illustrate how even a few observations can result in creative data representation. This tutorial will demonstrate how to efficiently visualize data from 30 different departments by employing various filters and functions to conclude departments’ attitudes toward seven questions. The decision to use DT for this dataset was based on its relative simplicity and user-friendliness compared to other table packages.
The tutorial starts by generating a table directly from the “Attitude” dataset. Next, the dataset benefits from creating two variables: Department and Ranking. After sorting and selecting the necessary variables to achieve a complete data table, the code adds filters and x- and y-axis bars. After properly formatting all variables and removing the row_names variable, the code adds filters and other additional functions, such as colors, proportional bars, and buttons for saving and printing the table in various formats.
The DT package is a valuable tool in data analysis, particularly because of the practicality of tables. Tables are one of the most commonly used tools in data analysis, providing a straightforward way to neatly organize data. Their simplicity and logical sorting order make it easier to understand specific points or anomalies in the data, especially with proper filtering, coloring, and additional modern features such as images and objects that can contain a large amount of metadata. While plots and graphs provide great visuals, data users will mostly turn to tables to deal with specific details in the dataset.
In this tutorial, you will learn how to sort, filter, and enhance the appearance of tables. Adding features like DT extensions, colors and proportional bars can make the table more visually appealing. The tutorial will provide the code sequence for each feature. Ultimately, the final table will result from a cumulative effort to add all the features, allowing for comparison with the original table. Mastering the DT package will inspire you to explore more advanced functions within the package and other table packages in R that require more advanced coding skills.
DT package and a practical example of utilizing filters with visible scroll, pagination, styling with selective highlights, adding proportional bars, and the ‘Button’ extension.
The DT package is a gateway to the JavaScript library DataTables with a plethora of initialization options. This package seamlessly integrates data tables with JSON (JavaScript Object Notation) and its Data Tables. It’s a powerful tool that allows users to showcase data, structure tables, or matrices as interactive tables in HTML formats, primarily through the Shiny app or R Markdown. Notably, Yihui Xie is the visionary behind this package, Shiny and R Markdown, among other packages and applications.
A basic example shows how the table, without any additional features, can be utilized to sort the values in the columns. Sorting is achieved by clicking on individual columns’ names with numeric values. The button ‘Search’ provides some filtering options, which is particularly useful if one wants to view a factor variable, as you can see in Table 3.
In order to provide a more useful data analysis, we will create additional variables to complement the existing dataset. Two new variables, ‘Department’ and ‘ranking’ are added to the table. The ‘ranking’ variable acts as a factor variable and is composed of the values from the ‘rating’ column resulting into three new variables: ‘low’, ‘medium’, and ‘high’.
prep_table<-attitude #Add the department names as a new column
prep_table$Department<- paste("Department", 1:nrow(prep_table))
prep_table<- prep_table[, c("Department", setdiff(names(prep_table),
"Department"))]
#Placing Department as a first column #Creating high, medium, low ranking
rank_table <-prep_table
variable_to_rank <- prep_table$rating #-Inf to 61 = low, 62-69 = medium, 70 to Inf = high
custom_breaks <- c(-Inf, 61, 69, Inf) # Define custom cut points
# Create the ranking variable with custom cut points
prep_table$rank_custom <- cut(variable_to_rank,
breaks = custom_breaks,
labels = c("low", "medium", "high"),
include.lowest = TRUE)
prep_table <- prep_table[, c("Department", setdiff(names(prep_table), "Department"))]final_table <- prep_table %>% #selecting the variables of interest
select(Department,rank_custom,rating, complaints,privileges,learning,raises,
critical,advance)%>%
rename('Ranking' = 'rank_custom','Rating'= 'rating','Complaints' = 'complaints',
'Privileges' = 'privileges','Learning'='learning','Raises' = 'raises',
'Critical' = 'critical','Advance' ='advance') #formatting variablesIn particular, enhancing the completed table with key features, such as scroll bars on the x and y axes, will be beneficial for managing large datasets. The filter buttons will display bars showing the minimum and maximum values for each column with numeric data. We removed the row names numerical range, with the Department variable serving as a unique observation for each row.
datatable(final_table,
options = list(paging = TRUE,
pageLength = 5, # number of rows to output for each page
lengthMenu = c(5,10,15,20,25,30), # available numbers on the menu
scrollX = TRUE, # enable scrolling on X axis
scrollY = TRUE, # enable scrolling on Y axis
autoWidth = TRUE, # use smart column width handling
columnDefs = list(list(targets = '_all', #to configure the widths of the column
className = 'dt-center'))), #the cell content is centered
selection = 'single', # enable selection of a single row
filter = 'top', # include column filters at the top
rownames = FALSE, # don't show row numbers/names
caption = 'Table 3: Table with filters, x and y bars, and no row_names variable.')
What’s more, it can also be used to highlight desired categories
to draw attention to specific values. In our case, we picked the’
Ranking ’Variable to define low, medium, and high values in that column
with different colors.
finaltable4<-final_table
datatable(finaltable4,
options = list(paging = TRUE,
pageLength = 5,
lengthMenu = c(5,10,15,20,25,30),
scrollX = TRUE,
scrollY = TRUE,
autoWidth = TRUE,
columnDefs = list(list(targets = '_all',
className = 'dt-center'))),
selection = 'single',
filter = 'top',
rownames = FALSE, #style table with colors
caption = 'Table 4: Table with added colors.') %>%
formatStyle(columns = "Ranking",background =styleEqual(c("low","medium","high"),
c("#ADD8E6","#4da1bc","#4a6771")))
In addition, this table package offers the convenience of
saving the table in CSV, Excel, or PDF formats, which is important for
anyone who wants to work on the table in a different context. The “dom”
extension arranges the elements around the table based on the specified
position: ‘Bfrtip’ includes Button, filter, processing display element,
table, table information summary, and pagination control. Depending on
where we want the desired extension, we can choose the character
ordering, which determines the layout of the elements around the table.
As a significant enhancement, we added the ‘stylecolorBar’ feature for
each column with numeric values, making them proportional to the values
in the cells, thereby enhancing data visualization.
finaltable5<-finaltable4
#adding buttons extensions for csv,excel,pdf,and copy and print
datatable(
finaltable5,
options = list(
paging = TRUE,
pageLength = 10,
lengthMenu = c(5, 10, 15, 20, 25, 30),
scrollX = TRUE,
scrollY = TRUE,
autoWidth = TRUE,
dom = 'Bfrtip', # dom defines position of elements around the table
buttons = c('copy', 'csv', 'excel', 'pdf', 'print'),
columnDefs = list(list(targets = '_all', className = 'dt-center'))
),
extensions = 'Buttons',
selection = 'single',
filter = 'top',
rownames = FALSE,
caption = 'Final Table'
) %>%
# Apply style for the Ranking column
formatStyle(
'Ranking', backgroundColor = styleEqual(
c("low", "medium", "high"),
c("#ADD8E6", "#4da1bc", "#4a6771")
)
) %>%
# Apply proportional bars to the other columns
formatStyle( # Exclude the first two columns
columns = names(finaltable5)[-c(1, 2)],
background = styleColorBar(range(finaltable5[,-c(1, 2)], na.rm = TRUE), color = '#FFCC99'),
backgroundSize = '88% 38%',
backgroundRepeat = 'no-repeat',
backgroundPosition = 'center'
)Table Notes:“From a survey of the clerical employees of a large financial organization, the data are aggregated from the questionnaires of the approximately 35 employees for each of 30 (randomly selected) departments. The numbers give the percent proportion of favourable responses to seven questions in each department.” attitude: The Chatterjee–Price Attitude Data
Variable description:
rating----------> Overall rating
complaints------> Handling of employee complaint
privileges------> Does not allow special privileges
learning--------> Opportunity to learn
raises----------> Raises based on performance
critical--------> Too critical
advance---------> Advancement
# Further Resources
Learn more about the DT package techniques with the following:
Leah Coffin (2023)Using the Datatable DT Package
Center for Quantitative Life Sciences, Oregon State University,by gibbond (2019) R-tips: A Table Makeover with DT
Yihui Xie (2018)Package ‘DT’
More Examples on Styling Cells, Rows, and Tables
This code through references and cites the following sources:
attitude: The Chatterjee–Price Attitude Data
Radar,AI edition, (2024) An R interface to the DataTables library
Devashree Madhugiri, (2022) Top 7 Packages for Making Beautiful Tables in R
DT: An R interface to the DataTables library
DataTables,Default styling options
Clare E. West,(2020)Making Tables Shiny: DT, formattable, and reactable
David Keyes (2022) How
to Make Beautiful Tables in R